from pymysql import connect


class dbUtil:
    def __init__(self):
        self.connection = None
        self.cursor = None

    def get_connectton(self):
        self.connection = connect(host='127.0.0.1', port=3306, db='bjsxt', user='root', password='root', charset='utf8')
        self.cursor = self.connection.cursor()

    def query_one(self, sql):
        self.get_connectton()
        self.cursor.execute(sql)
        result = self.cursor.fetchone()
        self.close()
        return result

    def query_by_page(self, sql, start_limit):
        self.get_connectton()
        self.cursor.execute(sql, start_limit)
        results = self.cursor.fetchall()
        self.close()
        return results

    def execute_dml(self, sql):
        self.get_connectton()
        self.cursor.execute(sql)
        self.connection.commit()
        self.close()

    def close(self):
        if self.cursor:
            self.cursor.close()

        if self.connection:
            self.connection.close()


if __name__ == '__main__':
    util = dbUtil()
    one = util.query_one('select * from employees')
    print(one)
    start_limit = (0, 10)
    all = util.query_by_page('select * from employees limit %s,%s', start_limit)
    for e in all:
        print(e)
    insert = 'insert into employees (employee_id,first_name,last_name,email,hire_date,job_id) values' \
             ' (11947, "刘", "家俊", "11947@qq.com", "2025-03-04 00:00:00", "IT_PROG")';
    util.execute_dml(insert)
